package com.GamersCom.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;


/**
 * Represents a query class to perform SQL Operations
 * @author Frank Sun
 *
 */

public class QueryMgr
{
	protected ConnMgr cm;	
	protected Connection conn ;	// A connection used to connect to the datasource
	protected Statement stmt ;	// a statement for building a query
	
	/**
     * Retrieve a list of table records based on the query passed in from the calling application.
     * @param query The query used to retrieve the data
     * @return the result set pointer used to fetch the data.
     */
    public ResultSet fetchList(String query) {
    	ResultSet rs = null;     	
    	try
		{
    		cm = ConnMgr.getInstance();			
			conn = cm.connect();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
    	
        return rs;
    }
    
    /**
     * Retrieve a list of table records based on the sql and params passed in from the calling application
     * @param query the query used to retrieve the data
     * @param paramList the param list assigned to the query
     * @return ResultSet the result set that fetched from the table
     */
    public ResultSet fetchList(String query, List paramList) {
    	ResultSet rs = null;
    	cm = ConnMgr.getInstance();
    	try
		{
			conn = cm.connect();
			stmt = conn.prepareStatement(query);
	    	for ( int i = 1; i <= paramList.size(); i++) 
	    	{	
	    		if ( paramList.get(i-1) instanceof String) {
	    			((PreparedStatement) stmt).setString(i, (String)paramList.get(i-1) );
	    		} else {
	    			((PreparedStatement) stmt).setObject(i, (Object)paramList.get(i-1));
	    		}
	    	}
System.out.println(stmt);
	    	
	    	rs = ((PreparedStatement) stmt).executeQuery();
	    	
	    	
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (ClassNotFoundException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	return rs;
    }
    
    /**
     * Execute a prepared query for a specific CRUD operation that is determined by the query.
     * @param query The query executed against the datasource
     * @param paramList The list of parameters passed to the prepared query
     * @throws SQLException if there is an error grabbing the connection or if there is an error retrieving the prepared statement
     * @throws ClassNotFoundException if there is an error finding the MySQL driver class
     */
    public void executePreparedQuery(String query, List paramList) 
    {    	
    	try
		{
    		cm = ConnMgr.getInstance();
			conn = cm.connect();
			stmt = (PreparedStatement) conn.prepareStatement(query);
	    	
	    	//populate the prepared query
	        for (int count = 1; count <= paramList.size(); count++) {
	            if (paramList.get(count - 1) instanceof String) {
	                ((PreparedStatement) stmt).setString(count, (String) paramList.get(count - 1));
	            } else {
	                ((PreparedStatement) stmt).setObject(count, paramList.get(count - 1));
	            }
	        }
System.out.println(stmt);	        
	    	((PreparedStatement) stmt).execute();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (ClassNotFoundException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    }         
    
}
